/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package database;

import java.sql.*;

/**
 * Class used to handle Database Queries
 *
 * @author Rick
 */
public class DBQueries {

    PreparedStatement ps;
    ResultSet rs;
    Connection conn;
    
    public DBQueries(){
        conn = SQLConfigeration.getConnection();
    }

    /**
     * Query to find number of visualizations
     * ordered by Country name, City name and then Visualization name
     *
     * @return result set containing query
     */
    public ResultSet getCountryCityName(){
        String query = "select count(u.visualName), c.country, c.city, u.visualName, u.timeStamp " +
                        "from Connection as c, Used as u " +
                        "where c.autoID = u.autoID " +
                        "group by c.country, c.city, u.visualName";

       try{
            ps = conn.prepareStatement(query);
            rs = ps.executeQuery();


       }catch(SQLException ex){
           System.out.println("SQL Error: " + ex);
       }
       return rs;
    }

    /**
     * Query Used to find number of visualizations viewed
     * ordered by visualization name, Country name and City name
     *
     * @return
     */
    public ResultSet getNameCountryCity(){
        String query = "select count(u.visualName), u.visualName, c.country, c.city, u.timeStamp "+
                       "from Connection as c, Used as u " +
                       "where c.autoId = u.autoID " +
                       "group by u.visualName, c.country, c.city";

       try{
            ps = conn.prepareStatement(query);
            rs = ps.executeQuery();


       }catch(SQLException ex){
           System.out.println("SQL Error: " + ex);
       }
       return rs;
    }

    /**
     * Query used to find number of visualizations viewed
     * ordered by Country name, City name, and username
     *
     * @return result set containg query
     */
    public ResultSet getCountryCityUsername(){
        String query = "select count(u.visualName), c.country, c.city, c.username, u.timeStamp " +
                       "from Connection as c, Used as u " +
                       "where c.autoId = u.autoID " +
                       "group by c.country, c.city, c.username";

       try{
            ps = conn.prepareStatement(query);
            rs = ps.executeQuery();


       }catch(SQLException ex){
           System.out.println("SQL Error: " + ex);
       }
       return rs;
    }

}
